-- EXEC sp_create_update_shipped_lot '710061','710061','71086-2',null,null,'SPL505YC256BTT',null,null,'Test','OSEP','11/05/2007','40000',null,null,null,null,null,'SPEL','FT',null,'0740089',null,null,null,null

ALTER proc sp_create_update_shipped_lot (
 
 @LOT_NUMBER 		VARCHAR(50),
 @VENDOR_LOT 		VARCHAR(50),  
 @CUSTOMER_PO 		VARCHAR(50),
 @CUSTOMER_SO 		VARCHAR(50),
 @INVOICE_NUMBER 	VARCHAR(50),
 @PART_NUMBER 		VARCHAR(50), 
 @LEAD_COUNT 		INT,
 @PACKAGE_TYPE 		VARCHAR(50),
 @CURRENT_LOCATION	VARCHAR(50),
 @NEXT_LOCATION 	NVARCHAR(100),
 @SHIP_DATE 		DATETIME,
 @SHIP_QTY 		INT,
 @MAWB 			VARCHAR(50),
 @HAWB 			VARCHAR(50),
 @FLIGHT_NUMBER 	VARCHAR(50),
 @ETD 			DATETIME,
 @ETA 			DATETIME,
 @VENDOR_NAME 		VARCHAR(50),
 @PS_TYPE 		VARCHAR(50),
 @WAFER_ID 		VARCHAR(200),
 @FAB_LOT_NUMBER 	VARCHAR(50),
 @SHIP_GOOD_DIE_QTY 	INT,
 @SHIPPING_DOC 		VARCHAR(50),
 @TESTER 		VARCHAR(50),
 @TEST_PROGRAM 		VARCHAR(50),
 @IS_DROP_SHIP    VARCHAR(5)	
 
 )
 
 
 as
 begin
 
 	declare @lot_id int
 	declare @process_step_id int
 	declare @location_id int	
 	DECLARE @NEXT_VENDOR_NAME NVARCHAR(100)
	
	DECLARE @WAFERIDS_AS_COMMA VARCHAR(200)	
	DECLARE @WAFER_IDS_TABLE   TABLE ( WAFER_ID INT )
	DECLARE @LOTCURSOR CURSOR
	DECLARE @WAFER_IDS VARCHAR(20)	

	IF @NEXT_LOCATION IS NOT NULL
	BEGIN			
			SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE V_NAME = @NEXT_LOCATION
			IF @NEXT_VENDOR_NAME IS NULL
			BEGIN
					SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT VENDOR_ID FROM ALTERNATE_VENDOR_NAMES WHERE ALTERNATE_VENDOR_NAME = @NEXT_LOCATION )
					IF @NEXT_VENDOR_NAME IS NULL
					BEGIN
							SET @NEXT_VENDOR_NAME = @NEXT_LOCATION
					END
			END
	END	
 	-- start -- get the lot id -- 
	if @current_location = 'Fab' 
 	begin
			IF @PS_TYPE IS NULL
			BEGIN
					SET @PS_TYPE = @current_location
			END
		 		 
 		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Fab' and PS_Type= @PS_TYPE and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 		if @process_step_id is null
 		begin
 			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Fab' and PS_Type= @PS_TYPE and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID
 			if @process_step_id is null
 			begin
 				IF @VENDOR_NAME = 'TSMC' OR @VENDOR_NAME = 'CHARTERED' OR @VENDOR_NAME = 'GRACE' OR @VENDOR_NAME IS NULL
				BEGIN
					IF @PROCESS_STEP_ID IS NULL
					BEGIN					
						SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'FAB' AND PS_TYPE = 'FAB OPTION' AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
						
						IF @PROCESS_STEP_ID IS NULL
						BEGIN
							SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'FAB' AND PS_TYPE = 'FAB OPTION' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID
						END	
					END
				END
				IF @PROCESS_STEP_ID IS NULL
				BEGIN
					--For Discrepancy Lot Notification Alert
					if ( select count(*) from Notification_Alert_Table where part_number = @part_number and Lot_Number = @lot_number and Fab_Lot_Number = @lot_number and Vendor_Name = @vendor_name and Process_Type = @ps_type and Is_USER_Notified = 1 ) = 0 
					begin
						INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
							VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @current_location,'Discrepancy Lot Notification', @SHIP_QTY, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
					end
					UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
		
					return
				END
 			end
 	   	end
		--The BELOW CODE ELSE BLOCK IS FOR CYPRESS SHIPED LOTS
		IF @vendor_name IS NOT NULL
		BEGIN
 	 		select @location_id = Location_ID from Location where L_Type = 'Fab' and L_Vendor_Name = @vendor_name
		END
		ELSE
		BEGIn
			 select @location_id = Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Number = @lot_number
			 IF @location_id IS NULL
			 BEGIN
					select TOP 1 @location_id = Location_ID from Process_step_Location_table where ProcessStep_ID = @process_step_id  AND Location_id In (44, 48)
					IF @location_id IS NULL
					BEGIN
						select @location_id = Location_ID from Location where L_Type = 'Fab' and L_Vendor_Name = 'CMI'
					END
			 END
			 SELECT @vendor_name = L_Vendor_Name FROM Location WHERE location_id = @location_id
		END
		
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		 
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By ,Lot_Category, Lot_Drop_Out, PO_Number) 
			values (@process_step_id, @location_id, @lot_number,@lot_number,@lot_number,@ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1 , 'PROD', 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
			
 		end
		else
		begin
			update Lot_Table Set PO_Number = ISNULL(PO_Number,@CUSTOMER_PO ), lot_current_status = 'Close' where Lot_Id = @lot_id
		end
 	end

	/* For sort*/
	if @current_location = 'Sort' OR @current_location = 'CP' 
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'Sort' and L_Vendor_Name = @vendor_name
 
 		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Sort'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 		if @process_step_id is null
 		begin
 			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Sort' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		 
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Good_Die_Qty, Lot_Drop_Out, PO_Number ) 
			values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', @ship_good_die_qty, 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
		end
		else
		begin
				update Lot_Table Set  PO_Number = ISNULL(PO_Number,@CUSTOMER_PO ) where Lot_Id = @lot_id
		end

		IF @NEXT_VENDOR_NAME IN (SELECT L_VENDOR_NAME FROM LOCATION WHERE L_TYPE = 'FAB' )
		BEGIN
			
			SET @WAFERIDS_AS_COMMA = REPLACE(@WAFER_ID, ';',',')
			INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )

			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_IDS
			WHILE @@FETCH_STATUS = 0
			BEGIN
				UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = 0 WHERE LOT_ID = @lot_id AND WAFER_ID = @WAFER_IDS
				
				SET @WAFER_IDS = NULL
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_IDS
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
		END
		
		Update Sort_lot set SL_Test_Program = ISNULL(@Test_program,SL_Test_Program) , Sl_Tester = ISNULL(@Tester, Sl_Tester ) where Lot_Id = @lot_id
 	end
	
	/* For Assembly*/
	if @current_location = 'Assembly' 
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'Assembly' and L_Vendor_Name = @vendor_name
  		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and  PS_Type_Name like 'Assembly'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 		if @process_step_id is null
 		begin
 			select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Assembly' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		 
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Drop_Out, PO_Number) 
				values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
		end
		else
		begin
				update Lot_Table Set  PO_Number = ISNULL(PO_Number,@CUSTOMER_PO ) where Lot_Id = @lot_id
		end
	end
	
	
	if @current_location = 'TEST' OR @current_location = 'FT'
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'TEST' and L_Vendor_Name = @vendor_name
		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and Ps_TYPE = @PS_TYPE AND PS_Type_Name = 'TEST'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID
		if @process_step_id is null
 		begin
 			select top 1 @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and Ps_TYPE = @PS_TYPE and PS_Type_Name ='TEST' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
			
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
				
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Drop_Out, PO_Number) 
				values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
		end
		else
		begin
				update Lot_Table Set  PO_Number = ISNULL(PO_Number,@CUSTOMER_PO ) where Lot_Id = @lot_id
		end
	end
	/* For PRETEST AND TAPE AND REEEL */
	if @current_location = 'CLASS STORE' 
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'CLASS STORE' and L_Vendor_Name = @vendor_name
  		select @process_step_id = PS.ProcessStep_ID from product P,Process_Step PS where P.part_number = @part_number and Ps_TYPE = @PS_TYPE and PS_Type_Name = 'CLASS STORE'  and P.ProcessStep_ID=PS.ProcessStep_ID 
 		if @process_step_id is null
 		begin
 			select @process_step_id = PS.ProcessStep_ID from Product_Map_Table PMT,Process_Step PS where PMT.part_number = @part_number and Ps_TYPE = @PS_TYPE and PS_Type_Name='CLASS STORE' and PMT.ProcessStep_ID=PS.ProcessStep_ID 
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		 
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Drop_Out, PO_Number) 
				values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
		end
		else
		begin
				update Lot_Table Set  PO_Number = ISNULL(PO_Number,@CUSTOMER_PO ) where Lot_Id = @lot_id
		end
	end

	if @current_location = 'EOL'
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'Class Store' and L_Vendor_Name = @vendor_name
		select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and Ps_TYPE = @PS_TYPE AND PS_Type_Name = 'Class Store'  and product.ProcessStep_ID=Process_Step.ProcessStep_ID
		if @process_step_id is null
 		begin
 			select top 1 @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and Ps_TYPE = @PS_TYPE and PS_Type_Name ='Class Store' and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
			
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
				
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Drop_Out, PO_Number) 
				values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0, @CUSTOMER_PO )
			select @lot_id = @@IDENTITY
		end
		else
		begin
				update Lot_Table Set  PO_Number = @CUSTOMER_PO where Lot_Id = @lot_id
		end
	end

	/* For FG*/
	if @current_location = 'Finished Goods' OR @current_location = 'FG' 
 	begin
 		select @location_id = Location_ID from Location where L_Type = 'Finished Goods' and L_Vendor_Name = @vendor_name
  		select @process_step_id = PS.ProcessStep_ID from product P,Process_Step PS where P.part_number = @part_number and PS_Type_Name = 'Finished Goods'  and P.ProcessStep_ID=PS.ProcessStep_ID 
 		if @process_step_id is null
 		begin
 			select @process_step_id = PS.ProcessStep_ID from Product_Map_Table PMT,Process_Step PS where PMT.part_number = @part_number and PS_Type_Name = 'Finished Goods' and PMT.ProcessStep_ID=PS.ProcessStep_ID
 			if @process_step_id is null
 			begin
 				-- dummy update to avoid SQLException
 				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 				return
 			end
 	   	end
 	 	
 		select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 		 
 		if @lot_id is null
 		begin
 			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_In, Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, Lot_Drop_Out) 
				values (@process_step_id, @location_id, @lot_number,@fab_lot_number, @lot_number, @ship_qty, @ship_date, 'Close', CURRENT_TIMESTAMP, 1, 'PROD', 0 )
			select @lot_id = @@IDENTITY
		end
	end
	 	
 	if @lot_id is null
 	begin
 		-- dummy update to avoid SQLException "Statements that produce result sets are not allowed in batch commands."
 		UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 	end
 	else   		
 	begin
		declare @shippedLotID int
		declare @shippedlotCount int
		DECLARE @GENERATE_SHIP_LOT_BY_WAFER_ID VARCHAR(5)
		set @shippedlotCount = 0
		SELECT @GENERATE_SHIP_LOT_BY_WAFER_ID = ISNULL(PROPERTY_VALUE,'FALSE') FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.operations.shippedLot.showShippedLotByWafer'

		IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE' AND @current_location IN ( 'FAB', 'SORT', 'CP', 'Bumping' )
		BEGIN
			SET @WAFERIDS_AS_COMMA = REPLACE(@WAFER_ID, ';',',')
			INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )

			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_IDS
			WHILE @@FETCH_STATUS = 0
			BEGIN
					select @shippedlotCount = count(ShippedLot_ID) from Shipped_Lot where Lot_ID = @lot_id and Status = 'Close' and Ship_Status > 0 and Ship_Date = @ship_date and Ship_Qty = 1 AND WAFER_ID = @WAFER_IDS
			
					if ( @shippedlotCount = 0 )
					begin
						
						select @shippedLotID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @ship_date and Ship_Qty = 1 AND WAFER_ID = @WAFER_IDS
						--if @shippedLotID is null 
	 					if ( @@rowcount = 0 )
	 					begin
							
	 	       				insert into Shipped_Lot( Lot_ID, Ship_Date, Ship_Qty, timestamp, Status, Ship_Status, Ship_Good_Die_Qty, Ship_Drop_Out, SL_Customer_SO, SL_Customer_PO, SL_Invoice_Number, SL_Lead_Count, SL_Package, SL_NextLocation, SL_MAWB_NUMBER, SL_HAWB_NUMBER, SL_Flight_Number, SL_ETD, SL_ETA, WAFER_ID, WaferID_LotNo, SHIP_REQ_NO ) 
								values( @lot_id, @ship_date, 1, current_timestamp, 'TRANSIT', 0, @Ship_Good_Die_Qty, 0, @customer_so, @customer_po, @invoice_number, @lead_count, @package_type, @NEXT_VENDOR_NAME, @mawb, @hawb, @flight_number, @etd, @eta,@WAFER_IDS, @vendor_Lot,@SHIPPING_DOC  )
							set @shippedLotID = ( select @@IDENTITY )

							IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @fab_lot_number AND Notification_Name= 'Transit Lot Notification' AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @current_location AND IS_USER_NOTIFIED = 1 ) = 0 
							BEGIN			
								INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Ship_Qty, Ship_Die_Qty, Ship_To_Location, Wafer_id, Status,TimeStamp, Ship_Date )
									VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @current_location,'Transit Lot Notification', @ship_qty, @Ship_Good_Die_Qty, @NEXT_VENDOR_NAME, @WAFER_IDS, 'Open', CURRENT_TIMESTAMP, @SHIP_DATE )
							END
						end
						else
						begin
							UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = 1, Ship_Good_Die_Qty = @Ship_Good_Die_Qty, Ship_Drop_Out = 0,
								SL_NextLocation = @NEXT_VENDOR_NAME, SL_Flight_Number = @flight_number, SL_ETD = @etd, SL_ETA = @eta, Wafer_Id = @WAFER_IDS, SL_Invoice_Number = @invoice_number, SL_Customer_SO = @customer_so, SL_Customer_PO = @customer_po, WaferID_LotNo = @vendor_Lot, SHIP_REQ_NO = @SHIPPING_DOC  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
						end 
					end
					else
					begin
						-- dummy update to avoid SQLException "Statements that produce result sets are not allowed in batch commands."
						UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
					end	
				
				SET @WAFER_IDS = NULL
				SET @shippedlotCount = NULL
				SET @shippedLotID = Null
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_IDS
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
		END
		ELSE
		BEGIN

			select @shippedlotCount = count(ShippedLot_ID) from Shipped_Lot where Lot_ID = @lot_id and Status = 'Close' and Ship_Status > 0 and Ship_Date = @ship_date and Ship_Qty = @ship_qty
			
			if ( @shippedlotCount = 0 )
			begin
				
				select @shippedLotID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @ship_date and Ship_Qty = @ship_qty
				--if @shippedLotID is null 
	 			if ( @@rowcount = 0 )
	 			begin
					IF (@CURRENT_LOCATION = 'FINISHED GOODS' OR @current_location = 'FG' ) AND @IS_DROP_SHIP = 'TRUE'
  					BEGIN
						insert into Shipped_Lot( Lot_ID, Ship_Date, Ship_Qty, timestamp, Status, Ship_Status, Ship_Good_Die_Qty, Ship_Drop_Out, SL_Customer_SO, SL_Customer_PO, SL_Invoice_Number, SL_Lead_Count, SL_Package, SL_NextLocation, SL_MAWB_NUMBER, SL_HAWB_NUMBER, SL_Flight_Number, SL_ETD, SL_ETA, WAFER_ID, WaferID_LotNo, SHIP_REQ_NO )
							values( @lot_id, @ship_date, @ship_qty, current_timestamp, 'Close', 1, @Ship_Good_Die_Qty, 0, @customer_so, @customer_po, @invoice_number, @lead_count, @package_type, @NEXT_VENDOR_NAME, @mawb, @hawb, @flight_number, @etd, @eta,@wafer_id, @vendor_Lot,@SHIPPING_DOC  )
						set @shippedLotID = ( select @@IDENTITY )
					END
					ELSE
					BEGIN
	 	       			insert into Shipped_Lot( Lot_ID, Ship_Date, Ship_Qty, timestamp, Status, Ship_Status, Ship_Good_Die_Qty, Ship_Drop_Out, SL_Customer_SO, SL_Customer_PO, SL_Invoice_Number, SL_Lead_Count, SL_Package, SL_NextLocation, SL_MAWB_NUMBER, SL_HAWB_NUMBER, SL_Flight_Number, SL_ETD, SL_ETA, WAFER_ID, WaferID_LotNo, SHIP_REQ_NO ) 
							values( @lot_id, @ship_date, @ship_qty, current_timestamp, 'TRANSIT', 0, @Ship_Good_Die_Qty, 0, @customer_so, @customer_po, @invoice_number, @lead_count, @package_type, @NEXT_VENDOR_NAME, @mawb, @hawb, @flight_number, @etd, @eta,@wafer_id, @vendor_Lot,@SHIPPING_DOC  )
						set @shippedLotID = ( select @@IDENTITY )
					END
								
					INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Ship_Qty, Ship_Die_Qty, Ship_To_Location, Wafer_id, Status,TimeStamp, Ship_Date )
						VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @current_location,'Transit Lot Notification', @ship_qty, @Ship_Good_Die_Qty, @NEXT_VENDOR_NAME, @wafer_Id, 'Open', CURRENT_TIMESTAMP, @SHIP_DATE )
				end
				else
				begin
					UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = @SHIP_QTY, Ship_Good_Die_Qty = @Ship_Good_Die_Qty, Ship_Drop_Out = 0,
						SL_NextLocation = @NEXT_VENDOR_NAME, SL_Flight_Number = @flight_number, SL_ETD = @etd, SL_ETA = @eta, Wafer_Id = @wafer_id, SL_Invoice_Number = @invoice_number, SL_Customer_SO = @customer_so, SL_Customer_PO = @customer_po, WaferID_LotNo = @vendor_Lot, SHIP_REQ_NO = @SHIPPING_DOC  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
				end 
			end
			else
			begin
				-- dummy update to avoid SQLException "Statements that produce result sets are not allowed in batch commands."
				UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
			end
		END--END OF LOT NUMBER BY WAFER ID	
 	end --END LOT ID IS NOT NULL
end
